﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml.Linq;

namespace UIProjectExcelToXml
{
  internal class Program
  {
    private static void Main(string[] args)
    {
      try
      {
        ReadOptions();
        DoWork();
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex);
      }
      Console.WriteLine("press any key to exit...");
      Console.ReadKey();
    }

    /// <summary>
    /// 是否移除空针脚
    /// </summary>
    private static bool _IsRemoveEmptyPin = false;

    private static void ReadOptions()
    {
      Console.WriteLine("请确认是否要移除无编号的空针脚。如不移除，则会修改针脚为'0'值。");
      Console.WriteLine("【0：不移除，替换为0值。】【其他：移除空针脚。】");
      _IsRemoveEmptyPin = (Console.ReadLine().Trim() != "0");
    }

    private static void DoWork()
    {
      string[] files = Directory.GetFiles(Environment.CurrentDirectory, "*.xlsx");
      foreach (var jf in files)
      {
        var fInfo = new FileInfo(jf);
        Console.WriteLine($"====================");
        Console.WriteLine($"即将开始转换文件。file={fInfo.FullName}");
        Console.WriteLine("请输入车型标识，如【BKWL/Verano2016-BCM】，结构为【车型预制体/车型-系统】");
        Console.WriteLine("可留空，会使用【defaultPart】内容替代。");
        Console.WriteLine("此值将会影响所有‘器件’绑定图片精灵的名字（前缀）。");
        string header = Console.ReadLine() ?? String.Empty;

        if (IsUseableHeader(header))
        {
          string xmlFile = ReadExcelFile(fInfo, header);
          Console.WriteLine($"完成转换Excel。xml={xmlFile}");
        }
        else
        {
          // 输入异常
          Console.WriteLine("您输入的车型标识名称不合法，请检查是否包含异常字符！");
          break;
        }
      }
    }

    /// <summary>
    /// 判断车型标识是否合法，一般是和文件名类似，不许出现特殊符号。
    /// </summary>
    private static bool IsUseableHeader(string header)
    {
      foreach (var jc in Path.GetInvalidFileNameChars())
      {
        if (header.Any(cc => cc == jc))
        {
          return false;
        }
      }
      return true;
    }

    private static string ReadExcelFile(FileInfo file, string header)
    {
      var xe = new XElement("System");
      using (ExcelPackage package = new ExcelPackage(file))
      {
        ExcelWorksheet sheet = package.Workbook.Worksheets[1];
        for (int i = 3; i <= sheet.Dimension.Rows; i++)
        {
          try
          {
            ReadRow(xe, sheet, i, header);
          }
          catch (Exception ex)
          {
            Console.WriteLine($"Error: row={i}, message={ex.Message}");
            throw;
          }
        }
      }
      string xmlFile = $"{file.FullName}.xml";
      xe.Save(xmlFile);
      return xmlFile;
    }

    private static void ReadRow(XElement xe, ExcelWorksheet sheet, int row, string header)
    {
      // C 针脚定义
      string name = sheet.Cells[$"C{row}"]?.Value?.ToString();
      if (!String.IsNullOrWhiteSpace(name))
      {
        // 此行数据有效
        // F - 器件编号，肯定不为空，如【1】
        string partId = GetMegeValue(sheet, row, 6).ToString();
        // upsert Part XElement
        var partXe = xe.Elements("Part").FirstOrDefault(dd => dd.Attribute("id").Value == partId);
        if (partXe == null)
        {
          // new part
          // B - 器件编号，如【B5LF】
          string partNo = GetMegeValue(sheet, row, 2)?.ToString();
          bool hasPartNo = !String.IsNullOrWhiteSpace(partNo);
          // A - 器件名称
          string partName = hasPartNo
            ? $"{partNo} - {GetMegeValue(sheet, row, 1)}"
            : GetMegeValue(sheet, row, 1)?.ToString();
          string partImage = "defaultPart";
          if (!String.IsNullOrWhiteSpace(header))
          {
            partImage = hasPartNo
              ? $"{header}_part{Int32.Parse(partId).ToString("00")}_{partNo}"
              : "defaultPart";
          }
          partXe = new XElement("Part"
            , new XAttribute("id", partId)
            , new XAttribute("Name", partName)
            , new XAttribute("ImagePrefabName", partImage)
            );
          xe.Add(partXe);
        }

        // G - 编程针脚号
        string pin_id = sheet.Cells[$"G{row}"].Value?.ToString();
        if (String.IsNullOrWhiteSpace(pin_id))
        {
          if (_IsRemoveEmptyPin)
          {
            // 空针脚，如果指定移除，则丢弃此行数据。
            return;
          }
          // 针脚号不存在，则设置为0
          pin_id = "0";
        }

        // insert Pin XElement
        var pinXe = new XElement("Pin"
          // C - 针脚定义
          , new XAttribute("Name", sheet.Cells[$"C{row}"].Value?.ToString() ?? "")
          // G - 编程针脚号
          , new XAttribute("id", pin_id ?? "")
          // D - 针脚号
          , new XAttribute("Pinid", sheet.Cells[$"D{row}"].Value?.ToString() ?? "")
          // E - 对应ECU针脚
          , new XAttribute("ECUid", sheet.Cells[$"E{row}"].Value?.ToString() ?? "")
          );

        partXe.Add(pinXe);

        var messages = new List<string>();
        // I - 支持断路
        string canConnect = sheet.Cells[$"I{row}"].Value?.ToString();
        if (!String.IsNullOrWhiteSpace(canConnect))
        {
          messages.Add("ButtonConnect:0");
          messages.Add("ButtonDisconnect:1");
        }
        // J - 支持搭铁
        string canBond = sheet.Cells[$"J{row}"].Value?.ToString();
        if (!String.IsNullOrWhiteSpace(canBond))
        {
          messages.Add("ButtonBonding:2");
        }
        pinXe.Add(new XAttribute("SendMessage", String.Join("|", messages)));
      }
    }

    public static object GetMegeValue(ExcelWorksheet wSheet, int row, int column)
    {
      string range = wSheet.MergedCells[row, column];
      if (range == null)
      {
        return wSheet.Cells[row, column].Value;
      }
      return wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;
    }
  }
}